"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import MySQLdb
import traceback
from msnp.persistence.mysql.ptp_accessors import PTPAccessor, PTPLog
from msnp.model import PTPModel
from msnp.persistence import FileAccessor
from msnp.logger import Logger
from msnp.persistence.mysql import DBErrorHandler

DATABASE = ""
USER = ""
PWD = ""
SCHEMA = ""
PATH_SQL_FILE = "../db_sql/ptp_sql/"

def __openConnection():
    """Opens a connection to the ptp database with the global arguments.
    
    Returns:
        Returns a tuple of (Connection, Cursor) or the tuple (None, None) if no connection could be set up.
    """
    
    database = MySQLdb.connect(DATABASE, USER, PWD, charset="utf8", use_unicode=True)
    if database != None:
        cursor = database.cursor()
        return (database, cursor)
    return (None, None)


def __closeConnection(database):
    """Close the connection to the database given as an argument.
    
    Args:
        database: The Connection object representing the database where the connection should be closed.
    """
    if database != None:
        database.close()
    else:
        Logger.info('database is None while closing')
        
        
def initDatabaseCredentials(host, username, password, schema):
    """Sets the ptp database credentials.
    
    Sets the global variables DATABASE, USER, PWD and SCHEMA
    to the corresponding given arguments.
    
    Args:
        host: A String representing the host name of the database. Default localhost.
        username: A String representing the user name. Default root.
        password: A String representing the password of the user. Default root.
        schema: A String representing the schema name. Default msnp_ptp.
    """
    #this method is a "setter" for these global variables. python interprets this as local variables without the "global"-statement.
    global DATABASE
    global USER
    global PWD
    global SCHEMA
    DATABASE = host
    USER = username
    PWD = password
    SCHEMA = schema
#    Logger.ptpDBinfo('database initialized')

def initSchema():
    """Initializes the ptp database.
    
    Opens a connection to the ptp database, drops all tables
    and creates them again (to ensure the tables have correct columns and are empty).
    Then, it closes the connection.
    """
    try:
        (database, cursor) = __openConnection()
        #delete tables and schema
        PTPLog.dropTable(database, cursor, SCHEMA)
        PTPAccessor.dropTables(database, cursor, SCHEMA)
        #insert tables and schema
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "schema.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "log.sql")
        PTPLog.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "user.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "group.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "message.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "genre.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "genre_values.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "user_group.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "member_group.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "user_message.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "group_genre.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
        query = FileAccessor.readFromFile(PATH_SQL_FILE + "chat_history.sql")
        PTPAccessor.executeQuery(database, cursor, query % (SCHEMA,))
    except:
        Logger.info('exception initializing schema in ptp database (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INIT, traceback.format_exc())
    finally:
        __closeConnection(database) 


def cleanDatabase():
    """Cleans the whole database.
    
    Deletes all entries of all tables of the ptp database
    
    Returns:
        Returns True if deletion was successful, or False if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                PTPAccessor.cleanDatabase(database, cursor, SCHEMA)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception cleaning ptp database (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.DELETE, traceback.format_exc())
    finally:
        __closeConnection(database)

#########
## LOG ##
#########

def log(description):
    """Writes the given String into the log table of the ptp database.
    
    Args:
        description: A String containing the log message.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                PTPLog.log(database, cursor, SCHEMA, description)
    finally:
        __closeConnection(database)

############
## BACKUP ##
############

def selectBackup(fromUsername):
    """Selects the specific backup belonging to the given user from the ptp database.
    
    Args:
        fromUsername: A String representing the user name from which the backup should be selected.
    
    Returns:
        A BackupContent object containing the backup of the given user stored in the ptp database is returned
            or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return PTPAccessor.selectBackup(database, cursor, SCHEMA, fromUsername)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting backup for username (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def processBackup(backupContent, overwriteNoneBackupFrom):
    """Inserts, updates or deletes the given backup to/from the ptp database.
    
    Depending on the type of the backup content it either calls the insertOrUpdateBackup function,
    deleteAllBackupsForUser function or deleteSelectedBackupsForUser function of the PTPAccessor module.
    
    Args:
        backupContent: A BackupContent object containing the backup to be inserted into the ptp database.
        overwriteNoneBackupFrom: True if the value None of the column backup_from should be overwritte, otherwise False.
    
    Returns:
        Returns True if the processing was successful, or False if an error occurred.
    """
    successful = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                type = backupContent.getType()
                if type == PTPModel.BackupType.UPDATE:
                    successful = PTPAccessor.insertOrUpdateBackup(database, cursor, SCHEMA, backupContent, overwriteNoneBackupFrom)
                elif type == PTPModel.BackupType.DELETE_ALL:
                    successful = PTPAccessor.deleteAllBackupsForUser(database, cursor, SCHEMA, backupContent.getFromUsername())
                elif type == PTPModel.BackupType.DELETE_SELECTED_BACKUPFROM:
                    successful = PTPAccessor.deleteSelectedBackupsForUser(database, cursor, SCHEMA, backupContent)
                else:
                    Logger.info("!!!!!!!!!!!!!!!!!no backuptype!!!!!!!!!!!!!!!")
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new backup (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return successful

def selectBackupFromUsers():
    """Selects all users, that have some backup content stored in the ptp database.
    
    Returns:
        A list of Strings representing all usernames, that store some backup in the ptp database ir returned
            or a list without any entries if an error occurred.
    """
    users = []
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                users = PTPAccessor.selectBackupFromUsers(database, cursor, SCHEMA)
    except:
        Logger.info('exception inserting new backup (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return users

############
## SELECT ##
############

def selectUserImResponsibleForUsername(username):
    """Selects a specific user from the database where the current user is responsible of.
    
    Args:
        username: A String representing the name of the searched user.
    
    Returns:
        A User object that contains the user corresponding to the username is returned or None if an error occurred.
    """
    user = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                user = PTPAccessor.selectUserImResponsibleForUsername(database, cursor, SCHEMA, username)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting user for username (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return user

def selectUserGroup(username, groupname, ownername):
    """Selects a specific user-group relation from the ptp database.
    
    Args:
        username: A String representing the user name.
        groupname: A String representing the group name.
        ownername: A String representing the owner name.
    
    Returns:
        A UserGroup object containing the corresponding user-group relation or None if an error occurred.
    """
    userGroup = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                userGroup = PTPAccessor.selectUserGroup(database, cursor, SCHEMA, username, groupname, ownername)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting userGroup for username and groupname (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return userGroup
    
def selectMessagesForUsername(username, delete):
    """Selects all messages for a specific username.
    
    Args:
        username: A String representing the user name.
        delete: A boolean value, True if the messages should be deleted, otherwise False.
    
    Returns:
        A list of MemberStatusChangedMessage objects belonging to the given user name or None if an error occurred.
    """
    messages = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                messages = PTPAccessor.selectMessagesForUsername(database, cursor, SCHEMA, username, delete)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting messages for username (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return messages


def selectGroupsForOwnername(ownername):
    """Selects certain groups based on the owner name.
    
    Args:
        ownername: A String representing the name of the owner.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    groups = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                groups = PTPAccessor.selectGroupsForOwnername(database, cursor, SCHEMA, ownername)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting group for groupname (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return groups

def selectGroupForGroupnameAndOwnername(groupname, ownername):
    """Selects certain groups based on the group name and owner name.
    
    Args:
        groupname: A String representing the name of the group.
        ownername: A String representing the name of the owner.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    group = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                group = PTPAccessor.selectGroupForGroupnameAndOwnername(database, cursor, SCHEMA, groupname, ownername)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting group for groupname (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return group

def selectGroupsForUsername(username):
    """Selects certain groups based on the user name that is a member of the group (has a user-group relation).
    
    Args:
        username: A String representing the user name.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    groups = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                groups = PTPAccessor.selectGroupsForUsername(database, cursor, SCHEMA, username)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting groups for username (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return groups

def selectGroupsForDetails(details):
    """Selects certain groups based on certain details.
    
    Args:
        details: A dictionary of (String, String) containing the details (column name, column value).
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    groups = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                groups = PTPAccessor.selectGroupsForDetails(database, cursor, SCHEMA, details)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting all groups (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return groups

def selectAllGroups():
    """Selects all groups.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    groups = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                groups = PTPAccessor.selectAllGroups(database, cursor, SCHEMA)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting all groups (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return groups

def selectGroupsWithMembersToConfirm(ownername):
    """Selects the groups of a certain owner where at least one member has the status NOT_CONFIRMED.
    
    Args:
        ownername: A String containing the owner name.
    
    Returns:
        A list of Group objects is returned or None if an error occurred.
    """
    groups = None
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                groups = PTPAccessor.selectGroupsWithMembersToConfirm(database, cursor, SCHEMA, ownername)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting members to confirm (should not happen!!!)')
        Logger.info(traceback.format_exc())
    finally:
        __closeConnection(database)
    return groups

def selectPredefinedGenres():
    """Selects all predefined genres.
    
    Returns:
        A list of Genre objects is returned or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return PTPAccessor.selectPredefinedGenres(database, cursor, SCHEMA)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting all genre types')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None


def selectPredefinedGenreByName(genreName):
    """Selects the predefined genre that corrsponds to the given genre name.
    
    Args:
        genreName: A String containing the genre name.
    
    Returns:
        A list of Genre objects is returned or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return PTPAccessor.selectPredefinedGenreByName(database, cursor, SCHEMA, genreName)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting genre types by name')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectChatHistoryByPartnernameAndUsername(partnername, username):
    """Selects all chat messages of a certain user with a certain partner.
    
    Args:
        partnername: A String containing the partner name of the chat message.
        username: A String containing the user name of the chat message.
    
    Returns:
        A list of ChatMessage objects is returned or a void list if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return PTPAccessor.selectChatHistoryByPartnernameAndUsername(database, cursor, SCHEMA, partnername, username)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting chat history')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

############
## INSERT ##
############

def insertUser(user):
    """Inserts the given User object into the database.
    
    Args:
        user: The User object to be inserted.
    
    Returns:
        True is returned if the insertion was successful, or False if an error occurred.
    """
    isInserted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isInserted = PTPAccessor.insertUser(database, cursor, SCHEMA, user)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting new user (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isInserted

def insertMessages(username, messages):
    """Inserts the given messages into the database.
    
    The given list of Message objects is inserted and related to the given username.
    All stored messages, that are not in the list, will be deleted.
    
    Args:
        username: A String representing the user that has to be related with the messages.
        messages: A list of Message objects to be inserted.
    
    Returns:
        True is returned if the insertion was successful, or False if an error occurred.
    """
    isInserted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isInserted = PTPAccessor.insertMessages(database, cursor, SCHEMA, username, messages)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting messages (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isInserted

def establishGroup(group):
    """Inserts the given Group object into the database.
    
    Args:
        group: The Group object to be inserted.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the insertion was successful,
        PTPModel.ReturnType.GROUP_ALREADY_EXISTS if the group does exist already,
        or PTPModel.ReturnType.ERROR if an error occurred.
    """
    isInserted = PTPModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isInserted = PTPAccessor.establishGroup(database, cursor, SCHEMA, group)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception creating group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isInserted

def updateGroup(group):
    """Updates the corresponding Group object of the database with the values of the given Group object.
    
    Args:
        group: The Group object to be updated.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the update was successful, or PTPModel.ReturnType.ERROR if an error occurred.
    """
    isUpdated = PTPModel.ReturnType.ERROR
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isUpdated = PTPAccessor.updateGroup(database, cursor, SCHEMA, group)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception creating group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isUpdated


def insertMemberGroup(membername, groupname, status, timestamp, ownername, insertGroupIfNotExisting):
    """Inserts the given group-member relastion into the database.
    
    Args:
        membername: A String containing the member name.
        groupname: A String containing the group name.
        status: A String containing the status of the membership.
        timestamp: A float value containing the exact timestamp of the membership creation.
        ownername: A String containing the owner name.
        insertGroupIfNotExisting: A boolean value, True if the group should be created if not existing, False otherwise.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the insertion was successful,
            PTPModel.MemberStatusChangedReturnType.ERROR if an error occurred,
            PTPModel.MemberStatusChangedReturnType.GROUP_NOT_EXISTING if the group to update does not exist in the database,
            or PTPModel.MemberStatusChangedReturnType.ALREADY_MEMBER if the membership does already exist.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return PTPAccessor.insertMemberGroup(database, cursor, SCHEMA, membername, groupname, status, timestamp, ownername, insertGroupIfNotExisting)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting member into group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return PTPModel.MemberStatusChangedReturnType.ERROR


def insertUserGroupAndGroup(membername, groupname, ownername):
    """Inserts the given user-group relation into the database, and, if not already present, it inserts the group (only group name and owner).
    
    Args:
        membername: A String containing the member name.
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
    
    Returns:
        True is returned if the insertion was successful, False otherwise.
    """
    isInserted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isInserted = PTPAccessor.insertUserGroupAndGroup(database, cursor, SCHEMA, membername, groupname, ownername)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting member into group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isInserted

def insertChatMessage(username, partnername, chatMessage):
    """Inserts the given chat message and relates it to the given user name and partner name.
    
    Args:
        username: A String containing the user name.
        partnername: A String containing the user name of the chat partner.
        chatMessage: A String containing the chat message to be inserted.
    
    Returns:
        True is returned if the insertion was successful, False otherwise.
    """
    isInserted = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isInserted = PTPAccessor.insertChatMessage(database, cursor, SCHEMA, username, partnername, chatMessage)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting chat message (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isInserted

############
## UPDATE ##
############

def updateChatMessageForSendernameAndTimestamp(sendername, timestamp, status):
    """Updates the status of the chat message, that corresponds to the given user name of the sender and the given timestamp.
    
    Args:
        sendername: A String containing the user name of the sender.
        timestamp: A float value containing the exact timestamp of the sending.
        status: A String representing the new status the corresponding chat message should have.
    
    Returns:
        True is returned if the update was successful, False otherwise.
    """
    isUpdated = False
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isUpdated = PTPAccessor.updateChatMessageForSendernameAndTimestamp(database, cursor, SCHEMA, sendername, timestamp, status)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating chat message (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isUpdated

def setIsActiveGroupForGroupnameAndOwnername(groupname, ownername, isActive):
    """Updates the column "isActive" for the entry in the group table with the given group name and owner name.
    
    Args:
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
        isActive: Boolean value, True if the group is still active, False otherwise. 
    
    Returns:
        True is returned if the update was successful, False otherwise.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                isInserted = PTPAccessor.setIsActiveGroupForGroupnameAndOwnername(database, cursor, SCHEMA, groupname, ownername, isActive)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception setting delete flag for group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return isInserted

def updateMemberGroup(membername, groupname, ownername, status, timestamp):
    """Updates the corresponding group-member relation of the database with the given values.
    
    Args:
        membername: A String containing the member name.
        groupname: A String containing the group name.
        ownername: A String containing the owner name.
        status: A String containing the status of the membership.
        timestamp: A float value containing the exact timestamp of the membership creation.
    
    Returns:
        PTPModel.ReturnType.SUCCESSFUL is returned if the insertion was successful,
            PTPModel.MemberStatusChangedReturnType.ERROR if an error occurred,
            or PTPModel.MemberStatusChangedReturnType.GROUP_NOT_EXISTING if the group to update does not exist in the database.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                return PTPAccessor.updateMemberGroup(database, cursor, SCHEMA, membername, groupname, ownername, status, timestamp)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception updating member of group (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPTPDBError(DBErrorHandler.DBErrorType.UPDATE, traceback.format_exc())
    finally:
        __closeConnection(database)
    return PTPModel.MemberStatusChangedReturnType.ERROR
